package dao;

import Model.ChoreBean;
import Model.UserBean;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 *
 * @author wrjohns
 */
public class ChoreDAO 
{
    private static Connection DBConn;

    public static void init() throws SQLException 
    {
        try 
        {
            Class.forName("org.apache.derby.jdbc.ClientDriver");
        } 
        catch (ClassNotFoundException e) 
        {
            System.err.println(e.getMessage());
            System.exit(0);
        }
        DBConn = DriverManager.getConnection("jdbc:derby://localhost:1527/ChoreBoard");
    }

    public static void terminate() throws SQLException 
    {
        DBConn.close();
    }
    
    public static void logChore(UserBean user,ChoreBean chore) throws SQLException {
        Statement stmt = DBConn.createStatement();
        stmt.executeUpdate("update person set points="+(user.getPoints()+chore.getPriority())+" where username='"+user.getUserName()+"'");
        
    }
    
    public static void loadBean(ChoreBean bean) throws SQLException {
        Statement stmt = DBConn.createStatement();
        ResultSet rs = stmt.executeQuery("select * from chore where chorename='"+bean.getChoreName()+"'");
        rs.next();
        bean.setFrequency(rs.getString("frequency"));
        bean.setPriority(rs.getInt("Priority"));
        bean.setHouseName(rs.getString("housename"));
        bean.setStartDay(rs.getInt("startday"));
        bean.setStartMonth(rs.getInt("startmonth"));
        bean.setStartYear(rs.getInt("startyear"));
        bean.setFrequency(rs.getString("frequency"));
        bean.setChoreLastDoneDay(rs.getInt("lastday"));
        bean.setChoreLastDoneMonth(rs.getInt("lastmonth"));
        bean.setChoreLastDoneYear(rs.getInt("lastyear"));
    }
    
    public static void addChore(ChoreBean newChore) throws SQLException
    {
        Statement stmt = DBConn.createStatement();
        stmt.executeUpdate("insert into CHORE values ('" + 
                            newChore.getChoreName() + "','" +
                            newChore.getHouseName() + "'," +
                            newChore.getStartDay() + "," +
                            newChore.getStartMonth() + "," +
                            newChore.getStartYear() + "," + 
                            null + "," + null + "," + null + ",'" +
                            newChore.getFrequency() + "'," +
                            newChore.getPriority() + ")");
    }
    
    public static void deleteChore(ChoreBean chore) throws SQLException
    {
       Statement stmt = DBConn.createStatement();
       stmt.executeUpdate("delete from CHORE where CHORENAME='" + chore.getChoreName() + "'");
    }
  
    public static void editChore(ChoreBean chore) throws SQLException
    {
        Statement stmt = DBConn.createStatement();
        stmt.executeUpdate("update CHORE set chorename='" + chore.getChoreName() +
                            "', housename='" + chore.getHouseName() +
                            "', startday =" + chore.getStartDay() +
                            ", startmonth =" + chore.getStartMonth() +
                            ", startyear =" + chore.getStartYear() +
                            ", lastday =" + chore.getChoreLastDoneDay() +
                            ", lastmonth =" + chore.getChoreLastDoneMonth() +
                            ", lastyear =" + chore.getChoreLastDoneYear() +
                            ", frequency ='" + chore.getFrequency() +
                            "', priority =" + chore.getPriority() +
                            " where chorename='" + chore.getChoreName() + "'");
    }
  
}